package fr.greta.centreFormation.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import fr.greta.centreFormation.bean.Diplome;

public class DiplomeHome {

	public static ArrayList<Diplome> selectAllDiplome(){

		ArrayList<Diplome> collectionDeDiplome = new ArrayList<Diplome>();
		String select = "SELECT * FROM diplome";

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(select);
			ResultSet resultat = stmt.executeQuery();

			while(resultat.next()){

				Diplome d = new Diplome();
				d.setIdDiplome(resultat.getInt("id_diplome"));
				d.setNom(resultat.getString("nom_diplome"));
				collectionDeDiplome.add(d);

			}
			return collectionDeDiplome;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}


	}
	
	public static Diplome selectUnDiplomeByIdDiplome(int idDiplome){

		Diplome diplome = new Diplome();
		String select = "SELECT * FROM diplome WHERE id_diplome = ?";

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(select);
			stmt.setInt(1, idDiplome);
			
			ResultSet resultat = stmt.executeQuery();
			resultat.next();
			diplome.setIdDiplome(resultat.getInt(1));
			diplome.setNom(resultat.getString(2));
			
			return diplome;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

	}
	
	public static Diplome selectUnDiplomeByIdCandidat(int idCandidat){

		Diplome diplome = new Diplome();
		String select = "SELECT * FROM diplome d " +
				"INNER JOIN diplome_candidat dc ON d.id_diplome = dc.id_diplome " +
				"INNER JOIN candidat c on dc.id_candidat = c.id_candidat " +
				"WHERE c.id_candidat = ?";

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(select);
			stmt.setInt(1, idCandidat);
			
			ResultSet resultat = stmt.executeQuery();
			resultat.next();
			diplome.setDateObtention(resultat.getDate(5));
			diplome.setIdDiplome(resultat.getInt(1));
			diplome.setNom(resultat.getString(2));
			
			return diplome;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

	}

	public static int insertUnDiplome(Diplome diplome){
		
		int idDiplome = 0;
		String insert = "INSERT INTO diplome (nom_diplome) VALUES (?)";
		
		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt = connection.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);
			stmt.setString(1, diplome.getNom());
			stmt.executeUpdate();
			
			ResultSet resultat = stmt.getGeneratedKeys();
			resultat.next();
			idDiplome = resultat.getInt(1);
			return idDiplome;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}
		
		
	}

	public static boolean updateUnDiplome(Diplome diplome){
		
		String updateTableDiplome = "UPDATE diplome SET nom_diplome = ? WHERE id_diplome = ?";
		String updateTableDiplomeCandidat = "UPDATE diplome_candidat SET date_diplome = ? WHERE id_diplome = ?";
		
		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt1 = connection.prepareStatement(updateTableDiplome);
			stmt1.setString(1, diplome.getNom());
			stmt1.setInt(2, diplome.getIdDiplome());
			stmt1.executeUpdate();
			
			if(diplome.getDateObtention() != null){
				PreparedStatement stmt2 = connection.prepareStatement(updateTableDiplomeCandidat);
				Date dateDiplome = diplome.getDateObtention();
				java.sql.Date sqldateDiplome = new java.sql.Date(dateDiplome.getTime());
				stmt2.setDate(1, sqldateDiplome);
				stmt2.setInt(2, diplome.getIdDiplome());
				stmt2.execute();
			}

			return true;
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		
		
	}

	public static boolean deleteUnDiplome(Diplome diplome){
		
		String delete1 = "DELETE FROM diplome_candidat WHERE id_diplome = ?";
		String delete2 = "DELETE FROM diplome WHERE id_diplome = ?";
		
		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmt1 = connection.prepareStatement(delete1);
			stmt1.setInt(1, diplome.getIdDiplome());
			stmt1.execute();
			
			PreparedStatement stmt2 = connection.prepareStatement(delete2);
			stmt2.setInt(1, diplome.getIdDiplome());
			stmt2.executeUpdate();
			
			return true;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		
		
		
		
	}

}
